﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess;
using System.Data;
using System.Collections;
using EntityLibrary;

namespace EntityDaoLibrary
{
    public class MIItemRelationZLDAO : IEntityDAO
    {
        private IDataAccess data;
        public MIItemRelationZLDAO()
        {
        }
        public MIItemRelationZLDAO(IDataAccess data)
        {
            this.data = data;
        }


        #region IEntityDAO 成员

        public void insertEntity(DataSet entityData)
        {
            try
            {
                SqlStruct sqlStruct = new SqlStruct();
                ParamField[] pars;
                sqlStruct.SqlString = "INSERT INTO I_ITEMRELATIONZL (ITEMIDHIS,ITEMIDMED,OPID,IMPORTDT,TRANDT,PRE1,PRE2,PRE3) values (ITEMIDHIS,ITEMIDMED,OPID,IMPORTDT,TRANDT,PRE1,PRE2,PRE3)";
                pars = new ParamField[8];
                pars[0] = new ParamField("ITEMIDHIS", "ITEMIDHIS");
                pars[1] = new ParamField("ITEMIDMED", "ITEMIDMED");
                pars[2] = new ParamField("OPID", "OPID");
                pars[3] = new ParamField("IMPORTDT", "IMPORTDT");
                pars[4] = new ParamField("TRANDT", "TRANDT");
                pars[5] = new ParamField("PRE1", "PRE1");
                pars[6] = new ParamField("PRE2", "PRE2");
                pars[7] = new ParamField("PRE3", "PRE3");
                sqlStruct.ParamFields = pars;
                foreach (DataRow row in entityData.Tables["I_ITEMRELATIONZL"].Rows)
                {
                    data.ExecuteNonQuery(row, sqlStruct);
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        public void updateEntity(DataSet entityData)
        {
            try
            {
                if (entityData.GetChanges() == null)
                    return;
                SqlStruct sqlStruct = new SqlStruct();
                ParamField[] pars;
                foreach (DataRow row in entityData.GetChanges().Tables["I_ITEMRELATIONZL"].Rows)
                {
                    switch (row.RowState)
                    {
                        case DataRowState.Added:
                            sqlStruct.SqlString = "INSERT INTO I_ITEMRELATIONZL (ITEMIDHIS,ITEMIDMED,OPID,IMPORTDT,TRANDT,PRE1,PRE2,PRE3) values (ITEMIDHIS,ITEMIDMED,OPID,IMPORTDT,TRANDT,PRE1,PRE2,PRE3)";
                            pars = new ParamField[8];
                            pars[0] = new ParamField("ITEMIDHIS", "ITEMIDHIS");
                            pars[1] = new ParamField("ITEMIDMED", "ITEMIDMED");
                            pars[2] = new ParamField("OPID", "OPID");
                            pars[3] = new ParamField("IMPORTDT", "IMPORTDT");
                            pars[4] = new ParamField("TRANDT", "TRANDT");
                            pars[5] = new ParamField("PRE1", "PRE1");
                            pars[6] = new ParamField("PRE2", "PRE2");
                            pars[7] = new ParamField("PRE3", "PRE3");
                            sqlStruct.ParamFields = pars;
                            data.ExecuteNonQuery(row, sqlStruct);
                            break;
                        case DataRowState.Deleted:
                            sqlStruct.SqlString = "DELETE FROM I_ITEMRELATIONZL WHERE ITEMIDHIS=ITEMIDHIS";
                            pars = new ParamField[1];
                            pars[0] = new ParamField("ITEMIDHIS", "ITEMIDHIS");
                            sqlStruct.ParamFields = pars;
                            row.RejectChanges();
                            data.ExecuteNonQuery(row, sqlStruct);
                            break;
                        case DataRowState.Modified:
                            sqlStruct.SqlString = "UPDATE I_ITEMRELATIONZL SET ITEMIDMED=@ITEMIDMED,OPID=@OPID,IMPORTDT=@IMPORTDT,TRANDT=GETDATE( ),PRE1=@PRE1,PRE2=@PRE2,PRE3=@PRE3 WHERE ITEMIDHIS=@ITEMIDHIS";
                            pars = new ParamField[7];
                            pars[0] = new ParamField("@ITEMIDMED", "ITEMIDMED");
                            pars[1] = new ParamField("@OPID", "OPID");
                            pars[2] = new ParamField("@IMPORTDT", "IMPORTDT");
                            pars[3] = new ParamField("@PRE1", "PRE1");
                            pars[4] = new ParamField("@PRE2", "PRE2");
                            pars[5] = new ParamField("@PRE3", "PRE3");
                            pars[6] = new ParamField("@ITEMIDHIS", "ITEMIDHIS");
                            sqlStruct.ParamFields = pars;
                            data.ExecuteNonQuery(row, sqlStruct);
                            break;
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        public void deleteEntity(DataSet entityData)
        {
            try
            {
                SqlStruct sqlStruct = new SqlStruct();
                ParamField[] pars;
                sqlStruct.SqlString = "DELETE FROM I_ITEMRELATIONZL WHERE ITEMIDHIS=ITEMIDHIS";
                pars = new ParamField[1];
                pars[0] = new ParamField("ITEMIDHIS", "ITEMIDHIS");
                sqlStruct.ParamFields = pars;
                foreach (DataRow row in entityData.Tables["I_ITEMRELATIONZL"].Rows)
                {
                    data.ExecuteNonQuery(row, sqlStruct);
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        public DataSet FindByPrimaryKey(object strKeyValue)
        {
            try
            {
                SqlStruct sqlStruct = new SqlStruct();
                string sql = "";
                QueryParameterCollection para;
                MIItemRelationZLData ds = new MIItemRelationZLData();
                sql = "SELECT I_ITEMRELATIONZL.ITEMIDHIS,I_ITEMRELATIONZL.ITEMIDMED,I_ITEMRELATIONZL.OPID,I_ITEMRELATIONZL.IMPORTDT,I_ITEMRELATIONZL.TRANDT,I_ITEMRELATIONZL.PRE1,I_ITEMRELATIONZL.PRE2,I_ITEMRELATIONZL.PRE3 from I_ITEMRELATIONZL WHERE ITEMIDHIS=@ITEMIDHIS";
                para = new QueryParameterCollection(1);
                para.Add("@ITEMIDHIS", strKeyValue);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONZL");
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        #endregion
        public DataSet FindByPrimaryKey(ArrayList KeyValue)
        {
            try
            {
                SqlStruct sqlStruct = new SqlStruct();
                string sql = "";
                QueryParameterCollection para;
                MIItemRelationZLData ds = new MIItemRelationZLData();
                sql = "SELECT I_ITEMRELATIONZL.ITEMIDHIS,I_ITEMRELATIONZL.ITEMIDMED,I_ITEMRELATIONZL.OPID,I_ITEMRELATIONZL.IMPORTDT,I_ITEMRELATIONZL.TRANDT,I_ITEMRELATIONZL.PRE1,I_ITEMRELATIONZL.PRE2,I_ITEMRELATIONZL.PRE3 from I_ITEMRELATIONZL WHERE ITEMIDHIS=@ITEMIDHIS";
                para = new QueryParameterCollection(1);
                para.Add("@ITEMIDHIS", KeyValue[0]);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONZL");
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }
        public DataSet FindAll()
        {
            try
            {
                string sql;
                MIItemRelationZLData ds = new MIItemRelationZLData();
                //sql = "SELECT I_ITEMRELATIONZL.ITEMIDHIS,I_ITEMRELATIONZL.ITEMIDMED,I_ITEMRELATIONZL.OPID,I_ITEMRELATIONZL.IMPORTDT,I_ITEMRELATIONZL.TRANDT,I_ITEMRELATIONZL.PRE1,I_ITEMRELATIONZL.PRE2,I_ITEMRELATIONZL.PRE3 "
                //    + " ,D_SUMMARY_INFO.CHARGEITEM,D_SUMMARY_INFO.UNIT,D_SUMMARY_INFO.UNITPRICE,D_SUMMARY_INFO.SPELLNO"
                //    + " FROM I_ITEMRELATIONZL,D_SUMMARY_INFO "
                //    + " WHERE I_ITEMRELATIONZL.ITEMIDHIS=D_SUMMARY_INFO.ITEMID"
                //    + " ORDER BY I_ITEMRELATIONZL.ITEMIDHIS"
                //    ;

                sql = @"SELECT I_ITEMRELATIONZL.ITEMIDHIS,I_ITEMRELATIONZL.ITEMIDMED,I_ITEMRELATIONZL.OPID,I_ITEMRELATIONZL.IMPORTDT,I_ITEMRELATIONZL.TRANDT,I_ITEMRELATIONZL.PRE1,I_ITEMRELATIONZL.PRE2,I_ITEMRELATIONZL.PRE3,
                        YY_SFXXMK.name,YY_SFXXMK.xmdw,YY_SFXXMK.xmdj,YY_SFXXMK.xmgg,YY_SFXXMK.py
                        FROM I_ITEMRELATIONZL,YY_SFXXMK
                        WHERE I_ITEMRELATIONZL.ITEMIDHIS=YY_SFXXMK.id
                        ORDER BY name
                        ";
                data.ExecuteDataset(sql, ds, "I_ITEMRELATIONZL");
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }
        /*重载FindAll方法，用来区分门诊和住院，clinicType 1 门诊，2住院
         * 
         * 修改人：郑宝力
         * 
         * 修改日期：2007年6月20日
         * 
         * */
        public DataSet FindAll(int clinicType)
        {
            try
            {
                string sql;
                QueryParameterCollection para;
                MIItemRelationZLData ds = new MIItemRelationZLData();
                sql = "SELECT I_ITEMRELATIONZL.ITEMIDHIS,I_ITEMRELATIONZL.ITEMIDMED,I_ITEMRELATIONZL.OPID,I_ITEMRELATIONZL.IMPORTDT,I_ITEMRELATIONZL.TRANDT,I_ITEMRELATIONZL.PRE1,I_ITEMRELATIONZL.PRE2,I_ITEMRELATIONZL.PRE3 "
                    + " ,D_SUMMARY_INFO.CHARGEITEM,D_SUMMARY_INFO.UNIT,D_SUMMARY_INFO.UNITPRICE,D_SUMMARY_INFO.SPELLNO"
                    + " FROM I_ITEMRELATIONZL,D_SUMMARY_INFO "
                    + " WHERE I_ITEMRELATIONZL.ITEMIDHIS=D_SUMMARY_INFO.ITEMID AND I_ITEMRELATIONZL.CLINICTYPE=@CLINICTYPE"
                    + " ORDER BY I_ITEMRELATIONZL.ITEMIDHIS"
                    ;

                
                para = new QueryParameterCollection(1);
                para.Add("@CLINICTYPE", clinicType);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONZL");
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }
        public string GetItemID(string itemid)
        {
            try
            {
                string sql;
                SqlStruct sqlStruct = new SqlStruct();
                QueryParameterCollection para;
                //MIItemRelationZLData ds=new MIItemRelationZLData();
                DataSet ds = new DataSet();
                sql = "SELECT ITEMIDMED FROM I_ITEMRELATIONZL WHERE ITEMIDHIS=@ITEMIDHIS";
                para = new QueryParameterCollection(1);
                para.Add("@ITEMIDHIS", itemid);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONZL");
                if (ds.Tables[0].Rows.Count < 1)
                    throw new Exception("未检测到检治项目对应关系信息:" + itemid);
                return ds.Tables[0].Rows[0]["ITEMIDMED"].ToString();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }
        /*重载GetItemID方法，用来区分门诊和住院，clinicType 1 门诊，2住院
         * 
         * 修改人：郑宝力
         * 
         * 修改日期：2007年6月20日
         * 
         * */

        public string GetItemID(string itemid, int clinicType)
        {
            try
            {
                string sql;
                SqlStruct sqlStruct = new SqlStruct();
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                sql = "SELECT ITEMIDMED FROM I_ITEMRELATIONZL WHERE ITEMIDHIS=@ITEMIDHIS AND CLINICTYPE=@CLINICTYPE";
                para = new QueryParameterCollection(2);
                para.Add("@ITEMIDHIS", itemid);
                para.Add("@CLINICTYPE", clinicType);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONZL");
                if (ds.Tables[0].Rows.Count < 1)
                    throw new Exception("未检测到检治项目对应关系信息:" + itemid);
                return ds.Tables[0].Rows[0]["ITEMIDMED"].ToString();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }
        public void ImportNewData()
        {
            try
            {
                string sql;
                SqlStruct sqlStruct = new SqlStruct();
                //sql = "INSERT INTO I_ITEMRELATIONZL (ItemIDhis,ImportDT) ";
                //sql+=  " SELECT ITEMID,CURRENT TIMESTAMP FROM D_SUMMARY_INFO ";
                //sql+=	 " WHERE  NOT EXISTS(";
                //sql+=		 "SELECT ITEMIDHIS FROM I_ITEMRELATIONZL WHERE I_ITEMRELATIONZL.ITEMIDHIS=D_SUMMARY_INFO.ITEMID ";
                //sql+=	")";

                //sql = " INSERT INTO I_ITEMRELATIONZL (ITEMIDHIS,IMPORTDT,PRE1) " +
                //    " SELECT ITEMID,CURRENT TIMESTAMP,CASE SUMMARY WHEN '床位费' THEN '3' ELSE '2' END " +
                //    " FROM D_SUMMARY_INFO " +
                //    " WHERE NOT EXISTS( " +
                //    " SELECT ITEMIDHIS " +
                //    " FROM I_ITEMRELATIONZL " +
                //    " WHERE I_ITEMRELATIONZL.ITEMIDHIS=D_SUMMARY_INFO.ITEMID ) ";

                sql = @"INSERT INTO I_ITEMRELATIONZL (ITEMIDHIS,IMPORTDT,PRE1,ITEMIDMED,OPID,TRANDT, PRE2,PRE3) 
                        SELECT id,GETDATE( ),CASE xmlb WHEN '6' THEN '3' ELSE '2' END,'','',GETDATE( ),'',''
                        FROM YY_SFXXMK 
                        WHERE NOT EXISTS( 
                        SELECT ITEMIDHIS,IMPORTDT,PRE1,ITEMIDMED,OPID,TRANDT, PRE2,PRE3
                        FROM I_ITEMRELATIONZL 
                        WHERE I_ITEMRELATIONZL.ITEMIDHIS=YY_SFXXMK.id )
                        ";
                data.ExecuteNonQuery(sql);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        public DataSet DisplayCorrectInfo(DateTime dt)
        {
            try
            {
                DataSet ds = new DataSet();
                string sql = "";
//                sql = @"SELECT I_ITEMRELATIONZL.ITEMIDHIS,I_ITEMRELATIONZL.ITEMIDMED,I_ITEMRELATIONZL.OPID,I_ITEMRELATIONZL.IMPORTDT,I_ITEMRELATIONZL.TRANDT,I_ITEMRELATIONZL.PRE1,I_ITEMRELATIONZL.PRE2,I_ITEMRELATIONZL.PRE3,
//                        YY_SFXXMK.name,YY_SFXXMK.xmdw,YY_SFXXMK.xmdj,YY_SFXXMK.xmgg,YY_SFXXMK.py,YB_ZL_INFO.XMBM,YB_ZL_INFO.XMMC,YB_ZL_INFO.SFLB
//                        FROM I_ITEMRELATIONZL,YY_SFXXMK,YB_ZL_INFO
//                        WHERE I_ITEMRELATIONZL.ITEMIDHIS=YY_SFXXMK.id AND I_ITEMRELATIONZL.ITEMIDMED=YB_ZL_INFO.XMBM AND TRANDT>=@dt
//                        ORDER BY name
//                        ";
                sql = @"SELECT I_ITEMRELATIONZL.ITEMIDHIS as '医院编码',I_ITEMRELATIONZL.ITEMIDMED as '医保编码',I_ITEMRELATIONZL.TRANDT as '对照时间',
                        YY_SFXXMK.name as '医院名称','' as '剂型',YY_SFXXMK.xmdw as '单位',YY_SFXXMK.xmgg as '规格',YY_SFXXMK.xmdj as '价格',YB_ZL_INFO.XMBM as '中心编码',YB_ZL_INFO.XMMC as '中心名称',YB_ZL_INFO.SFLB as '收费类别',I_ITEMRELATIONZL.PRE1 as '中心项目类别'
                        FROM I_ITEMRELATIONZL,YY_SFXXMK,YB_ZL_INFO
                        WHERE I_ITEMRELATIONZL.ITEMIDHIS=YY_SFXXMK.id AND I_ITEMRELATIONZL.ITEMIDMED=YB_ZL_INFO.XMBM AND TRANDT>=@dt
                        ORDER BY name
                        ";
                QueryParameterCollection para = new QueryParameterCollection(1);
                para.Add("@dt", dt);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONZL");
                return ds;
            }
            catch (Exception e)
            {
                
                throw new Exception(e.Message, e);
            }

        }
    }
}
